#!/usr/bin/python3
# -*- coding: UTF-8 -*-
# https://www.runoob.com/python3/python-mysql-connector.html
# https://blog.csdn.net/qq_34935726/article/details/83039322
import time

import mysql.connector
import os
import platform
import sys

from pythonManger.util.configUtil import sysConfig

pro_path = os.path.split(os.path.realpath(__file__))[0]
if platform.system() == "Windows":
  sys.path.append(pro_path + '/util')
else:
  sys.path.append('/home/rootfile/gitcode/SmartHome/util')


def initMysql():
  global mydb
  global mycursor
  mydb = mysql.connector.connect(
    host=sysConfig()['db_host'],
    user=sysConfig()['db_user'],
    passwd=sysConfig()['db_pass'],
    database=sysConfig()['db']
  )
  mycursor = mydb.cursor()


'''
创建异常主表
'''


def creatCrashHandlerTable():
  global table_name
  table_name = sysConfig()['table_crash']
  sql_one = 'DROP TABLE IF EXISTS ' + table_name + ';'
  mycursor.execute(sql_one)
  sql = 'CREATE TABLE ' + table_name + ' (id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(255),creat_time DATETIME,update_time DATETIME,deleted VARCHAR(1) default 0,des VARCHAR(255),type_id VARCHAR(255),attach VARCHAR(255))'
  mycursor.execute(sql)
  mydb.commit()


'''
创建异常表类型
'''


def creatCrashHandlerTypeTable():
  global table_crash_type
  table_crash_type = sysConfig()['table_crash_type']
  sql_one = 'DROP TABLE IF EXISTS ' + table_crash_type + ';'
  mycursor.execute(sql_one)
  sql = 'CREATE TABLE ' + table_crash_type + ' (id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(255),creat_time DATETIME,deleted VARCHAR(1) default 0,des VARCHAR(255))'
  mycursor.execute(sql)
  mydb.commit()


# 添加数据
def addCrashModel(crashHandler):
  initMysql()
  table_name = sysConfig()['table_crash']
  creat_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
  update_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
  sql = "INSERT INTO " + table_name + " (name,creat_time,update_time,deleted,des,type_id,attach) VALUES (%s, %s, %s, %s, %s, %s, %s)"
  val = (crashHandler['name'], creat_time, update_time, crashHandler['deleted'],
         crashHandler['des'], crashHandler['type_id'], crashHandler['attach'])
  mycursor.execute(sql, val)
  mydb.commit()  # 数据表内容有更新，必须使用到该语句
  # print(mycursor.rowcount, "记录插入成功。")
  return mycursor.rowcount


def addCrashTypeModel(crashTypeHandler):
  initMysql()
  # print(crashTypeHandler)
  table_name = sysConfig()['table_crash_type']
  creat_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
  sql = "INSERT INTO " + table_name + " (name,creat_time,deleted,des) VALUES (%s, %s, %s, %s)"
  val = (crashTypeHandler['name'], creat_time, crashTypeHandler['deleted'], crashTypeHandler['des'])
  # print(sql,val)
  mycursor.execute(sql, val)
  mydb.commit()
  return mycursor.rowcount

'''
删除
'''
def delFun(data):
    table_name = sysConfig()['table_crash']
    if str(data['t']) == '1':
      table_name = sysConfig()['table_crash_type']
    cmd_sql = 'DELETE FROM '+ table_name +' where id = ' + str(data['id'])
    print('cmd_sql',cmd_sql)
    mycursor.execute(cmd_sql)
    mydb.commit()

def findCrashByPage(index, size, key):
  initMysql()
  table_name = sysConfig()['table_crash']
  print('index:'+str(index), size)
  i = int(index) - 1
  if key == '':
    sql = "SELECT * FROM "+table_name + " limit "+str(i*int(size))+" , "+str(size)+";"
  else:
    sql = 'SELECT * FROM '+table_name + ' WHERE name LIKE  "' + key + '%" limit '+str(i*int(size))+' , '+str(size)+';'
  # print('sql',sql)
  mycursor.execute(sql)
  myresult = mycursor.fetchall()  # fetchall() 获取所有记录
  return myresult

def findCrashTypeByPage(index, size, key):
  initMysql()
  table_name = sysConfig()['table_crash_type']
  # print('index:'+str(index), size)
  i = int(index) - 1
  if key == '':
    sql = "SELECT * FROM "+table_name + " limit "+str(i*int(size))+" , "+str(size)+";"
  else:
    sql = 'SELECT * FROM '+table_name + ' WHERE name LIKE  "' + key + '%" limit '+str(i*int(size))+' , '+str(size)+';'
  # print('sql',sql)
  mycursor.execute(sql)
  myresult = mycursor.fetchall()  # fetchall() 获取所有记录
  return myresult


def findCrashSize(key):
  table_name = sysConfig()['table_crash']
  if key == '':
    count_size =  "select count(id) from " + table_name
  else:
    count_size =  "select count(id) from " + table_name+ ' WHERE name LIKE  "' + str(key)+'%"'
  # print('count_size', count_size)
  mycursor.execute(count_size)
  myresult = mycursor.fetchall()
  return myresult

def findCrashTypeSize(key):
  table_name = sysConfig()['table_crash_type']
  if key == '':
    count_size =  "select count(id) from " + table_name
  else:
    count_size =  "select count(id) from " + table_name+ ' WHERE name LIKE  "' + str(key)+'%"'

  print(count_size)
  mycursor.execute(count_size)
  myresult = mycursor.fetchall()
  return myresult

# 异常数据列表
# id 自增
# name 异常名称
# creat_time 创建时间
# update_time 更新时间
# deleted 是否已删除 0、未删 1、已删
# des 备注
# type_id 类型id
# attach 附属信息 jsonstr

if __name__ == '__main__':
  # 创建表
  initMysql()
  try:
    creatCrashHandlerTable()
    creatCrashHandlerTypeTable()
  except Exception as e:
    mydb.rollback()
    print(e)
